﻿package com.file.servlet;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.file.dao.CityResultSet;

public class showservlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException {


       String SQL=(String)request.getSession().getAttribute("SQL");
       System.out.println("SQL--  "+SQL);
       CityResultSet crs=new CityResultSet();
       ResultSet Crs = crs.query(SQL);   
       String xlsName = "test1.xls";  
       String sheetName = "sheetName";  

       HSSFWorkbook workbook = new HSSFWorkbook();  
       HSSFSheet sheet = workbook.createSheet();  
       workbook.setSheetName(0, sheetName);  //����sheet����
       HSSFRow row = sheet.createRow((short) 0); //��һ�м�row��0��������Ϊshort����ǿ��ת��
       HSSFCell cell;  //excel ��ʽ
       try {  
          ResultSetMetaData md = Crs.getMetaData();  
          int nColumn = md.getColumnCount(); // //�õ���ݼ�������
           System.out.println("ncolumn"+nColumn);

      for (int i = 1; i <= nColumn; i++) {  
           cell = row.createCell((short) (i - 1));  
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
           cell.setCellValue(new HSSFRichTextString(md.getColumnLabel(i)));  //���ر���
      }  
      int iRow = 1;  
      while (Crs.next()) {  
           row = sheet.createRow((short) iRow); 
           System.out.println("rs ---0--"+Crs.getObject(1));
           System.out.println("rs ---0--"+Crs.getObject(2));
           for (int j = 1; j <= nColumn; j++) {  
               cell = row.createCell((short) (j - 1));  
               cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
               //System.out.println(j);//
               //System.out.println("rs"+rs.getObject(j));
        if(Crs.getObject(j)!=null){
                 cell.setCellValue(new HSSFRichTextString(Crs.getObject(j)  
                    .toString()));
         }
        else{
    	    System.out.println("isnull");
    	    cell.setCellValue(new HSSFRichTextString(""));}
    	   
        }  
        iRow++;   
    }  
       String filename = request.getRealPath("/") + xlsName;  
       request.setAttribute("filename", filename);  
       FileOutputStream fOut = new FileOutputStream(filename);  
       workbook.write(fOut);  
       fOut.flush();  
       fOut.close(); 
       request.getRequestDispatcher("openservlet").forward(request,  
               response); 
  
} catch (Exception e) {  
    e.printStackTrace();  
}  
}
	public void doPost(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException {
this.doGet(request, response); 


}
}